Attribute VB_Name = "Weekly_Escalation_Report_AB"
' ==============================================================================
' Specific type for handling
' ==============================================================================
Public Type TSCell_type
    Column As String
    Item As String
End Type

'==============================================================================================
'  Procedure used to extract the information from the "Weekly_Escalation_Report"
'  Last Modification : 27/03/2012
'  (Alain Bernhart)
' ==============================================================================================
Function Create_Weekly_Escalation_Report()
'Sub Create_Weekly_Escalation_Report()
  Dim TSCell(6) As TSCell_type
  Dim out As String
  Dim tmp As String
  Dim tmp_ar() As String
  Dim tmp_arr() As String
  Dim j, i, k   As Integer
  Dim srnbn, srnbo As Integer
  
  Dim srnb  As Integer
  Dim Customer, Country, BP, SR As String
  Dim Escalation_Area, First_Name, Last_Name, Escalation_Source, Status_Summary As String
  Dim MailTo, MailCC, MailAttachement_Prefix_xlsx_FileName, MailAttachement_Directory_xlsx_FileName, MailAttach As String
  
    '-------------------------------------------------------------------------------------------------
    '                                                 VAR
    '-------------------------------------------------------------------------------------------------
    
    MailTo = "BERNHART, ALAIN (ALAIN); BRUNEL, NICOLAS (NICOLAS); DE-JABRUN, GUILHEM (GUILHEM); DUPUY, FRANCK (FRANCK); Garceau, Martha P (Martha); Joyce, Rose Ann (Rose); Matthews, Charles C (Charles); NEGRI, JORGE MARCELO (JORGE MARCELO); SIMON, PASCAL (PASCAL); BABOT, VINCENT (VINCENT); BARBIER, NATHALIE (NATHALIE); BOHANNE, Philippe (Philippe)** CTR **; IVALDI, GUILLAUME (GUILLAUME); LING, RICHARD CL (RICHARD); OUSS, JEAN (JEAN); RINIE, HUBERT (HUBERT); ALLIVATO, KENNETH (KENNETH); BOUTROS, AMGAD (AMGAD); DUSSART, ANNE (ANNE); FAJERSSON, Gunnar (Gunnar); LEVY, MICHEL-JACQUES (MICHEL-JACQUES); RAKUSCHA, WOLFGANG (WOLFGANG); RAOUL, THIERRY (THIERRY); Schofield, Paul A (Paul); WAGNER, REMY (REMY); BONNIN, THIERRY (THIERRY); DESTOT, MATTHIEU (MATTHIEU)"
    MailCC = ""
    ' Example Attachment file: "Weekly escalation report Backlog  week 11_12.xlsx"
    MailAttachement_Prefix_xlsx_FileName = "Weekly escalation report Backlog  week "
    MailAttachement_Directory_xlsx_FileName = "C:\Documents and Settings\kluges1\My Documents\Technical Support\Escalation Manager\Work\"
    
    '----------
    ' Summary
    '----------
    TSCell(0).Column = "J"
    TSCell(0).Item = "Summary"
    
    '----------
    ' Escalalion AREA
    '----------
    TSCell(1).Column = "I"
    TSCell(1).Item = "Escalation_Area"
    
    '----------
    ' First Name
    '----------
    TSCell(2).Column = "P"
    TSCell(2).Item = "First_Name"
    
    '----------
    ' Last Name
    '----------
    TSCell(3).Column = "O"
    TSCell(3).Item = "Last_Name"
    
    '----------
    ' Escalalion Source
    '----------
    TSCell(4).Column = "D"
    TSCell(4).Item = "Escalation_Source"
      
    '----------
    ' Status Summary
    '----------
    TSCell(5).Column = "K"
    TSCell(5).Item = "Status_Summary"
    
    '-------------------------------------------------------------------------------------------------
    '                                                 CODE
    '-------------------------------------------------------------------------------------------------
    
    'WeekNb = "W" & Right(Year(Now), Len(Year(Now)) - 2) & DateDiff("w", DateSerial(Year(Date), 1, 1), Date)
    'WeekNb = "W" & Right(Year(Now), Len(Year(Now)) - 2) & DateDiff("w", DateSerial(Year(Date), 1, 1), Date) + 1
     WeekNb = "W" & DateDiff("w", DateSerial(Year(Date), 1, 1), Date) & "/" & Right(Year(Now), Len(Year(Now)) - 2)
            
    ' File name Month Year
    MailAttach = MailAttachement_Directory_xlsx_FileName & MailAttachement_Prefix_xlsx_FileName & DateDiff("w", DateSerial(Year(Date), 1, 1), Date) & "_" & Right(Year(Now), Len(Year(Now)) - 2) & ".xlsx"
            
    out = "<html> <head> <title></title> </head> <body>"
    
    out = out & "Hello," & "<br>" & "<br>"
    out = out & "Please find attached the Weekly Enterprise escalation report for Week :" & WeekNb & "<br>" & "<br>"
    out = out & "<u> Reminder </u> <br>"
    out = out & "   Call every Tuesday at 5 pm ((GMT+01:00) Brussels, Copenhagen, Madrid, Paris.) <br>"
    out = out & "   Bridge:  2801 2801 access code: 9960325 <br> <br>"
    out = out & "<i> This is an abbreviated version.  For more information, see attachment file. </i>"
   
    srnbn = 0
    srnbo = 0
    srnb = 0
    For j = 2 To Cells(65536, 1).End(xlUp).Row
        If Not Rows(j).Hidden Then
            For i = 0 To 5
                If Cells(j, TSCell(i).Column).Value <> Empty Then
                    
                    If TSCell(i).Item = "Summary" Then
                    
                        '----------------------
                        ' Number of Escalation
                        '----------------------
                        If Cells(j, TSCell(i).Column).Font.Bold = True Then
                            If srnbn = 0 Then
                                out = out & "<br>" & "<br>"
                                out = out & "<u> <b>" & "ADDED THIS WEEK" & "</b> </u>" & "<br>" & "<br>"
                            End If
                            srnbn = srnbn + 1
                            srnb = srnbn
                        Else
                            If srnbo = 0 Then
                                out = out & "<br>" & "<br>"
                                out = out & "<u> <b>" & "EXISTING ISSUES" & "</b> </u>" & "<br>" & "<br>"
                            End If
                            srnbo = srnbo + 1
                            srnb = srnbo
                        End If
 
                        ' Split the Cellule in: Customer, Country, BP, SR
                        tmp = Cells(j, TSCell(i).Column).Value
                        tmp_ar = Split(tmp, VBA.Chr(10))
                        For k = 0 To UBound(tmp_ar)
                            If k = 0 Then
                                Customer = UCase(tmp_ar(k))
                            End If
                            If k = 1 Then
                                Country = UCase(tmp_ar(k))
                                tmp_arr = Split(Country, "COUNTRY:")
                                Country = tmp_arr(1)
                            End If
                            If k = 2 Then
                                BP = tmp_ar(k)
                            End If
                            If k = 3 Then
                                SR = tmp_ar(k)
                            End If
                        Next k
                    End If

                    If TSCell(i).Item = "Escalation_Area" Then
                        Escalation_Area = UCase(Cells(j, TSCell(i).Column).Value)
                    End If
                        
                    If TSCell(i).Item = "First_Name" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        First_Name = StrConv(tmp, vbProperCase)
                    End If
            
                    If TSCell(i).Item = "Last_Name" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        Last_Name = StrConv(tmp, vbProperCase)
                    End If
            
                    If TSCell(i).Item = "Escalation_Source" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        Escalation_Source = UCase(tmp)
                    End If
            
                    If TSCell(i).Item = "Status_Summary" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        Status_Summary = Replace(tmp, VBA.Chr(10), " ")
                    End If
                End If
            Next i
            out = out & "<b>" & srnb & ") " & Customer & "</b>" & "<br>"
            out = out & "    " & Escalation_Area & " - " & Country & " - " & BP & " - (" & First_Name & " " & Last_Name & ")" & "<br>"
            out = out & "    " & "Escalation Source: " & Escalation_Source & "<br>"
            out = out & "    " & Status_Summary & "<br>" & "<br>"
            
        End If
     Next j
     
     
    out = out & "Best regards" & "<br>" & "<br>"
    out = out & "Alain Bernhart" & "<br>"

    out = out & "</body> </html>"

    ' Create an Outlook application.
    Set OL = CreateObject("Outlook.Application")
        
    ' Create a new MailItem.
    Set MyItem = OL.CreateItem(olMailItem)
        
    With MyItem
        .To = MailTo
        .Cc = MailCC
        .Subject = "Weekly escalation report: " & WeekNb
        .HTMLBody = out
        .Categories = "Banking-Info"
    End With
    If ExisteFichier(MailAttach) Then
        MyItem.Attachments.Add (MailAttach)
    Else
        MsgBox "The file :" & VBA.Chr(13) & MailAttachement_Prefix_xlsx_FileName & DateDiff("w", DateSerial(Year(Date), 1, 1), Date) & "_" & Right(Year(Now), Len(Year(Now)) - 2) & ".xlsx" & VBA.Chr(13) & " doesn't exist in your directory" & VBA.Chr(13) & MailAttachement_Directory_xlsx_FileName
    End If
    MyItem.Display


End Function

'==============================================================================================
'  Procedure used to extract the information from Excel file
' ==============================================================================================
Function Weekly_Escalation_Report_Text() As String

  Dim TSCell(8) As TSCell_type
  
  Dim out As String
  Dim Out_NewLine_Space1 As String
  Dim Out_NewLine_Space2 As String
    
  Dim tmp As String
  Dim tmp_ar() As String
  Dim tmp_arr() As String
  Dim j, i, k   As Integer
  
  Dim srnb  As Integer
  Dim Summary, Escalation_Area, First_Name, Last_Name, Escalation_Source, Status_Summary As String
  Dim Country, End_Customer, Summary, Product, End_Customer_Old As String
  
  Dim MailTo, MailCC As String
  
    '-------------------------------------------------------------------------------------------------
    '                                                 VAR
    '-------------------------------------------------------------------------------------------------
       
    Out_NewLine_Space1 = "&nbsp;" & "&nbsp;" & "&nbsp;"
    Out_NewLine_Space2 = "&nbsp;" & "&nbsp;" & "&nbsp;" & "&nbsp;" & "&nbsp;"
    
    '----------
    ' End Customer
    '----------
    TSCell(0).Column = "AA"
    TSCell(0).Item = "End_Customer"
       
    '----------
    ' Escalalion AREA
    '----------
    TSCell(1).Column = "I"
    TSCell(1).Item = "Escalation_Area"
    
    '----------
    ' First Name
    '----------
    TSCell(2).Column = "P"
    TSCell(2).Item = "First_Name"
    
    '----------
    ' Last Name
    '----------
    TSCell(3).Column = "O"
    TSCell(3).Item = "Last_Name"
    
    '----------
    ' Escalalion Source
    '----------
    TSCell(4).Column = "D"
    TSCell(4).Item = "Escalation_Source"
      
    '----------
    ' Status Summary
    '----------
    TSCell(5).Column = "K"
    TSCell(5).Item = "Status_Summary"
   
    '----------
    ' Country
    '----------
    TSCell(6).Column = "H"
    TSCell(6).Item = "Country"
    
    '----------
    ' Product
    '----------
    TSCell(7).Column = "B"
    TSCell(7).Item = "Product"
    
    '----------
    ' Next Action
    '----------
    TSCell(8).Column = "M"
    TSCell(8).Item = "Next_Action"
    
    '-------------------------------------------------------------------------------------------------
    '                                                 CODE
    '-------------------------------------------------------------------------------------------------
    srnb = 0
    End_Customer_Old = ""
    
    For j = 2 To Cells(65536, 1).End(xlUp).Row
        If Not Rows(j).Hidden Then
            If Cells(j, TSCell(0).Column).Value <> Empty Then
                For i = 0 To 8
    
                    If TSCell(i).Item = "End_Customer" Then
                            srnb = srnb + 1
                            If Cells(j, TSCell(i).Column).Value <> End_Customer_Old Then
                                End_Customer = Cells(j, TSCell(i).Column).Value
                                End_Customer_Old = End_Customer
                                srnb = 1
                            Else
                                End_Customer = ""
                            End If
                                
                        End If
 
                    If TSCell(i).Item = "Escalation_Area" Then
                        Escalation_Area = UCase(Cells(j, TSCell(i).Column).Value)
                    End If

                    If TSCell(i).Item = "First_Name" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        First_Name = StrConv(tmp, vbProperCase)
                    End If
            
                    If TSCell(i).Item = "Last_Name" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        Last_Name = StrConv(tmp, vbProperCase)
                    End If

                    If TSCell(i).Item = "Escalation_Source" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        Escalation_Source = UCase(tmp)
                    End If
            
                    If TSCell(i).Item = "Status_Summary" Then
                        tmp = Cells(j, TSCell(i).Column).Value
                        Status_Summary = Replace(tmp, VBA.Chr(10), " ")
                    End If
                    
                      
                    If TSCell(i).Item = "Country" Then
                        Country = Cells(j, TSCell(i).Column).Value
                    End If
            
                    If TSCell(i).Item = "Product" Then
                        Product = Cells(j, TSCell(i).Column).Value
                    End If
            
                    If TSCell(i).Item = "SR_Severity" Then
                        SR_Severity = Cells(j, TSCell(i).Column).Value
                    End If
            
                    If TSCell(i).Item = "Summary" Then
                            tmp = Cells(j, TSCell(i).Column).Value
                            tmp_ar = Split(tmp, VBA.Chr(10))
                            Summary = tmp_ar(0)
                            'Summary = StrConv(tmp_ar(0), vbProperCase)
                    End If
                    
                    If TSCell(i).Item = "Next_Action" Then
                            tmp = Cells(j, TSCell(i).Column).Value
                            Next_Action = Replace(tmp, VBA.Chr(10), "<br> " & Out_NewLine_Space2 & "&nbsp;" & "&nbsp;")
                    End If
                    
                    If TSCell(i).Item = "Action_Point" Then
                            Action_Point = Cells(j, TSCell(i).Column).Value
                    End If
                    
                Next i
                
                If End_Customer <> "" Then
                    out = out & "<b>" & End_Customer & "</b>"
                    out = out & "<br>"
                End If
                    
                    
             out = out & "<b>" & srnb & ") " & Customer & "</b>" & "<br>"
            out = out & "    " & Escalation_Area & " - " & Country & " - " & BP & " - (" & First_Name & " " & Last_Name & ")" & "<br>"
            out = out & "    " & "Escalation Source: " & Escalation_Source & "<br>"
            out = out & "    " & Status_Summary & "<br>" & "<br>"
            
            
            
            End If
            
        End If
     Next j

    Weekly_Escalation_Report_Text = out

End Function
